<?php
	require_once( 'core_DataBaseConnect.php' );
	require_once( '_configs.php' );
	
	class ModelDAO
	{
		// 코드 중복 체크
		public static function isAvailableModel( DataBaseConnect $con, $modelCode )
		{
			$query = 'SELECT COUNT(SEQ) FROM '.configs::TABLE_MODEL." WHERE `CODE`='$modelCode';";
			$result = $con->query( $query );
			
			$row = DataBaseConnect::fetch_assoc($result);
			
			return ($row[ 'COUNT(SEQ)' ] == '0');
		}
		// 이름&생일 중복 체크
		public static function isAvailableModel2( DataBaseConnect $con, $name, $date )
		{
			$query = 'SELECT COUNT(SEQ) FROM '.configs::TABLE_MODEL." WHERE `NAME`='$name' AND `BIRTH_DATE`='$date';";
			$result = $con->query( $query );
			
			$row = DataBaseConnect::fetch_assoc($result);
			
			return ($row[ 'COUNT(SEQ)' ] == '0');
		}
		// 새 모델 추가
		public static function insertModel( DataBaseConnect $con, 
											$code, 
											$name, 
											$date, 
											$region, 
											$education, 
											$educationMemo, 
											$sex,
											$military,
											$militaryMemo,
											$height,
											$ref,
											$refLicense,
											$ownerID )
		{
			$query = 'INSERT INTO '.configs::TABLE_MODEL.
						"(
							`SEQ` ,
							`CODE` ,
							`NAME` ,
							`BIRTH_DATE` ,
							`BIRTH_REGION` ,
							`LAST_EDU` ,
							`LAST_EDU_MEMO` ,
							`SEX`,
							`MILITARY`,
							`MILITARY_MEMO`,
							`HEIGHT`,
							`REF`,
							`REF_LICENSE`,
							`OWNER_ID`
						)
						VALUES 
						(
							NULL,
							'$code',
							'$name',
							'$date',
							'$region',
							'$education',
							'$educationMemo',
							'$sex',
							'$military',
							'$militaryMemo',
							'$height',
							'$ref',
							'$refLicense',
							'$ownerID'
						);";
			return $con->query( $query );
		}
		
		
		// 프로필 사진 갱신
		public static function updateModelPicturePath( DataBaseConnect $con, 
														$model_code, 
														$picturePath )
		{
			$query = 'UPDATE '.configs::TABLE_MODEL." SET  `PICTURE`='$picturePath' WHERE `CODE`='$model_code' LIMIT 1 ;";
			return $con->query( $query );
		}
		
		
		// 추가 썸네일 경로 갱신
		public static function insertModelThumbs( DataBaseConnect $con, 
													$model_code, 
													$thumbnails )
		{
			foreach( $thumbnails as $k=>$v )
			{
				$query = 'INSERT INTO '.configs::TABLE_MODELTHUMBS.
							"(
								`SEQ` ,
								`M_CODE` ,
								`LINK`
							)
							VALUES 
							(
								NULL,
								'$model_code',
								'$v'
							);";
				$con->query( $query );
			}
		}
		
		// 전체 ROW 수
		public static function countModels( DataBaseConnect $con, $rankCode, $dutyCode, $partyCode )
		{
			if( $rankCode || $dutyCode )
			{
				// 조건에 의한다.
				$query 	= 'SELECT COUNT(Distinct `M_CODE`) FROM '.configs::TABLE_MODELDUTY;
				$query 	.=		' WHERE';
				
				if( $rankCode )
				{
					$query 	.=		" `R_CODE`='$rankCode'";
					if( $dutyCode )
						$query 	.=		" AND";
				}
				if( $dutyCode )
				{
					$query 	.=		" `D_CODE`='$dutyCode'";
				}
				$query 	.= ';';
				$fetch = DataBaseConnect::fetch_assoc( $con->query( $query ) );
				return $fetch[ 'COUNT(Distinct `M_CODE`)' ];
			}else if( $partyCode && $partyCode != configs::NULL_PARTY_CODE )
			{
				// 조건에 의한다.
				$query 	= 'SELECT COUNT(Distinct `M_CODE`) FROM '.configs::TABLE_MODELPARTY;
				$query 	.=		' WHERE';
				$query 	.=		" `P_CODE`='$partyCode'";
				$query 	.= ';';
				$fetch = DataBaseConnect::fetch_assoc( $con->query( $query ) );
				return $fetch[ 'COUNT(Distinct `M_CODE`)' ];
			}else{
				// 모든 모델 정보를 대상으로 한다.
				$query 	= 'SELECT COUNT(*) FROM '.configs::TABLE_MODEL.';';
				$fetch = DataBaseConnect::fetch_assoc( $con->query( $query ) );
				return $fetch[ 'COUNT(*)' ];
			}
		}
		
		// 모든 목록 가져오기
		public static function selectModels( DataBaseConnect $con,
												$page,
												$numPerPage, 
												$rankCode, 
												$dutyCode,
												$partyCode )
		{
			$startIndex = max( ($page-1)*$numPerPage, 0 );
			if( $rankCode || $dutyCode )
			{
				// 조건에 의한다.
				// 1. 관계있는 
				$query 	= 'SELECT Distinct `M_CODE` FROM '.configs::TABLE_MODELDUTY;
				$query 	.=		' WHERE';
				
				if( $rankCode )
				{
					$query 	.=		" `R_CODE`='$rankCode'";
					if( $dutyCode )
						$query 	.=		" AND";
				}
				if( $dutyCode )
				{
					$query 	.=		" `D_CODE`='$dutyCode'";
				}
				$query = 'SELECT * FROM '.configs::TABLE_MODEL.
							" WHERE `CODE` IN ($query) LIMIT $startIndex,$numPerPage;";
				return $con->query( $query );
			}else if( $partyCode && $partyCode != configs::NULL_PARTY_CODE )
			{
				// 조건에 의한다.
				$query 	= 'SELECT Distinct `M_CODE` FROM '.configs::TABLE_MODELPARTY.
							" WHERE `P_CODE`='$partyCode'";
				$query = 'SELECT * FROM '.configs::TABLE_MODEL.
							" WHERE `CODE` IN ($query) LIMIT $startIndex,$numPerPage;";
				return $con->query( $query );
			}else{
				// 모든 모델 정보를 대상으로 한다.
				$query = 'SELECT * FROM '.configs::TABLE_MODEL." LIMIT $startIndex,$numPerPage;";
				return $con->query( $query );
			}
		}
		
		// 단일 항목 가져오기
		public static function selectModel( DataBaseConnect $con,
												$model_code )
		{
			$query = 'SELECT * FROM '.configs::TABLE_MODEL." WHERE `CODE`='$model_code' LIMIT 1;";
			return $con->query( $query );
		}
		
		// 모델에 설정된 모든 썸네일 정보를 가져옴
		public static function selectModelThumnails( DataBaseConnect $con,
														$model_code )
		{
			$query = 'SELECT * FROM '.configs::TABLE_MODELTHUMBS." WHERE `M_CODE`='$model_code';";
			return $con->query( $query );
		}
		
		// 대표 이미지 경로
		public static function selectPicturePath( DataBaseConnect $con,
														$model_code )
		{
			$query = 'SELECT PICTURE FROM '.configs::TABLE_MODEL." WHERE `CODE`='$model_code' LIMIT 1;";
			return $con->query( $query );
		}
		
		// 썸네일 이미지 경로
		public static function selectThumbPath( DataBaseConnect $con,
														$thumb_seq )
		{
			$query = 'SELECT LINK FROM '.configs::TABLE_MODELTHUMBS." WHERE `SEQ`='$thumb_seq' LIMIT 1;";
			return $con->query( $query );
		}
		
		// 썸네일 정보를 삭제
		public static function deleteThumbPath( DataBaseConnect $con,
														$thumb_seq )
		{
			$query = 'DELETE FROM '.configs::TABLE_MODELTHUMBS." WHERE `SEQ`='$thumb_seq' LIMIT 1;";
			return $con->query( $query );
		}
		
		
		
		// 모델 정보 업데이트
		public static function updateModel(	DataBaseConnect $con,
											$code,
											$name,
											$picture,
											$date, 
											$region, 
											$education, 
											$educationMemo, 
											$sex,
											$military,
											$militaryMemo,
											$height,
											$ref,
											$refLicense )
		{
			$query = 'UPDATE '.configs::TABLE_MODEL."
			 			SET
							`NAME`='$name',
							`PICTURE`='$picture',
							`BIRTH_DATE`='$date',
							`BIRTH_REGION`='$region',
							`LAST_EDU`='$education',
							`LAST_EDU_MEMO`='$educationMemo',
							`SEX`='$sex',
							`MILITARY`='$military',
							`MILITARY_MEMO`='$militaryMemo',
							`HEIGHT`='$height',
							`REF`='$ref',
							`REF_LICENSE`='$refLicense'
						WHERE
							`CODE`='$code'
						LIMIT 1;";
			return $con->query( $query );
		}
		
		// 모델 정당 입력
		public static function insertModelParty( DataBaseConnect $con,
												 $code,
												 $startDate,
												 $endDate,
												 $partyCode,
												 $memo )
		{
			$query = 'INSERT INTO '.configs::TABLE_MODELPARTY." 
						(
							`M_CODE`,
							`START`,
							`END`,
							`P_CODE`,
							`MEMO`
						)
						VALUES
						(
							'$code',
							'$startDate',
							'$endDate',
							'$partyCode',
							'$memo'
						);";
			return $con->query( $query );
		}
		
		// 모델 정당 조회
		public static function selectModelParty( DataBaseConnect $con,
												 $code )
		{
			$query = 'SELECT * FROM '.configs::TABLE_MODELPARTY." WHERE `M_CODE`='$code'";
			return $con->query( $query );
		}
		
		
		// 모델 정당 삭제
		public static function deleteModelParty( DataBaseConnect $con,
												 $seq )
		{
			$query = 'DELETE FROM '.configs::TABLE_MODELPARTY." WHERE `SEQ`='$seq' LIMIT 1;";
			return $con->query( $query );
		}
		
		// 모델 직책 입력
		public static function insertModelDuty( DataBaseConnect $con,
												 $code,
												 $startDate,
												 $endDate,
												 $partyCode,
												 $rankCode,
												 $dutyCode,
												 $memo,
												 $completedCode )
		{
			$query = 'INSERT INTO '.configs::TABLE_MODELDUTY." 
						(
							`M_CODE`,
							`START`,
							`END`,
							`P_CODE`,
							`R_CODE`,
							`D_CODE`,
							`MEMO`,
							`COM_CODE`
						)
						VALUES
						(
							'$code',
							'$startDate',
							'$endDate',
							'$partyCode',
							'$rankCode',
							'$dutyCode',
							'$memo',
							'$completedCode'
						);";
			return $con->query( $query );
		}
		
		// 모델 직책 조회
		public static function selectModelDuty( DataBaseConnect $con,
												 $code )
		{
			$query = 'SELECT * FROM '.configs::TABLE_MODELDUTY." WHERE `M_CODE`='$code'";
			return $con->query( $query );
		}
		
		
		// 모델 직책 삭제
		public static function deleteModelDuty( DataBaseConnect $con,
												 $seq )
		{
			$query = 'DELETE FROM '.configs::TABLE_MODELDUTY." WHERE `SEQ`='$seq' LIMIT 1;";
			return $con->query( $query );
		}
		
		// 모델 약력 입력
		public static function insertModelHistory( DataBaseConnect $con,
												 $code,
												 $startDate,
												 $endDate,
												 $historyCode,
												 $memo,
												 $order )
		{
			$query = 'INSERT INTO '.configs::TABLE_MODELHISTORY." 
						(
							`M_CODE`,
							`START`,
							`END`,
							`H_CODE`,
							`MEMO`,
							`ORDER`
						)
						VALUES
						(
							'$code',
							'$startDate',
							'$endDate',
							'$historyCode',
							'$memo',
							'$order'
						);";
			return $con->query( $query );
		}
		
		// 모델 약력 조회
		public static function selectModelHistory( DataBaseConnect $con,
												 $code )
		{
			$query = 'SELECT * FROM '.configs::TABLE_MODELHISTORY." WHERE `M_CODE`='$code' ORDER BY `ORDER`;";
			return $con->query( $query );
		}
		
		
		// 모델 약력 삭제
		public static function deleteModelHistory( DataBaseConnect $con,
												 $seq )
		{
			$query = 'DELETE FROM '.configs::TABLE_MODELHISTORY." WHERE `SEQ`='$seq' LIMIT 1;";
			return $con->query( $query );
		}
		
		// 모델 약력 우선순위 갱신
		public static function updateModelHistoryOrder( DataBaseConnect $con,
												 $seq,
												 $order )
		 {
			 $query = 'UPDATE '.configs::TABLE_MODELHISTORY."
			 				SET
								`ORDER`='$order'
							WHERE
								`SEQ`='$seq'
							LIMIT 1;";
			return $con->query( $query );
		 }
		
		
		// 모델 당선기록 입력
		public static function insertModelElection( DataBaseConnect $con,
												 $code,
												 $date,
												 $regionCode,
												 $partyCode,
												 $isProp,
												 $completedCode )
		{
			$query = 'INSERT INTO '.configs::TABLE_MODELELECTION." 
						(
							`M_CODE`,
							`DATE`,
							`G_CODE`,
							`P_CODE`,
							`IS_PROP`,
							`COM_CODE`
						)
						VALUES
						(
							'$code',
							'$date',
							'$regionCode',
							'$partyCode',
							'$isProp',
							'$completedCode'
						);";
			return $con->query( $query );
		}
		
		// 모델 당선기록 조회
		public static function selectModelElection( DataBaseConnect $con,
												 $code )
		{
			$query = 'SELECT * FROM '.configs::TABLE_MODELELECTION." WHERE `M_CODE`='$code'";
			return $con->query( $query );
		}
		
		
		// 모델 당선기록 삭제
		public static function deleteModelElection( DataBaseConnect $con,
												 $seq )
		{
			$query = 'DELETE FROM '.configs::TABLE_MODELELECTION." WHERE `SEQ`='$seq' LIMIT 1;";
			return $con->query( $query );
		}
		
		
		// 모델 사업 연관 추가
		public static function insertModelBusiness( DataBaseConnect $con,
												 	$code,
													$businessCode,
													$actionCode )
		{
			$query = 'INSERT INTO '.configs::TABLE_MODELBUSINESS." 
						(
							`M_CODE`,
							`B_CODE`,
							`A_CODE`
						)
						VALUES
						(
							'$code',
							'$businessCode',
							'$actionCode'
						);";
			return $con->query( $query );
		}
		
		// 모델 사업 연관 조회
		public static function selectModelBusiness( DataBaseConnect $con,
													$code )
		{
			// JOIN 쿼리
			$query = 'SELECT a.SEQ, a.M_CODE, a.B_CODE, a.A_CODE, b.NAME AS `B_NAME` 
						FROM '.configs::TABLE_MODELBUSINESS.' AS a JOIN '.configs::TABLE_BUSINESS." AS b 
						ON 
							 a.B_CODE=b.CODE
						WHERE 
							`M_CODE`='$code';";
			return $con->query( $query );
		}
		
		
		// 모델 사업 연관 삭제
		public static function deleteModelBusiness( DataBaseConnect $con,
													 $seq )
		{
			$query = 'DELETE FROM '.configs::TABLE_MODELBUSINESS." WHERE `SEQ`='$seq' LIMIT 1;";
			return $con->query( $query );
		}
		
		// 모델 사업 중복 체크
		public function isValidateBusiness( DataBaseConnect $con,
											$code,
											$bCode )
		{
			$query = 'SELECT COUNT(SEQ) FROM '.configs::TABLE_MODELBUSINESS." WHERE `M_CODE`='$code' AND `B_CODE`='$bCode';";
			$fetch = DataBaseConnect::fetch_assoc( $con->query( $query ) );
			
			return ((int)$fetch[ 'COUNT(SEQ)' ] == 0);
		}
		
		// SNS 링크 추가
		public static function insertModelSns(	DataBaseConnect $con,
												$code, 
												$snsCode,
												$link )
		{
			$query = 'INSERT INTO '.configs::TABLE_MODELSNS."
						(
							`M_CODE`,
							`S_CODE`,
							`LINK`
						)VALUES(
							'$code',
							'$snsCode',
							'$link'
						);";
			return $con->query( $query );
		}
		
		// SNS 링크 조회
		public static function selectModelSns( DataBaseConnect $con, $modelCode )
		{
			$query = 'SELECT * FROM '.configs::TABLE_MODELSNS." WHERE `M_CODE`='$modelCode';";
			return $con->query( $query );
		}
		
		// SNS 링크 삭제
		public static function deleteModelSns( DataBaseConnect $con, $seq )
		{
			$query = 'DELETE FROM '.configs::TABLE_MODELSNS." WHERE `SEQ`='$seq' LIMIT 1;";
			return $con->query( $query );
		}
		
		// 태그 존재 여부
		public static function hasModelTag(	DataBaseConnect $con,
													$code, 
													$tag )
		{
			$query = 'SELECT COUNT(SEQ) FROM '.configs::TABLE_MODELTAGS."
						WHERE
							`M_CODE`='$code'
							AND
							`TAG`='$tag';";
			$fetch = DataBaseConnect::fetch_assoc( $con->query( $query ) );
			return ( $fetch[ 'COUNT(SEQ)' ] != '0' );
		}
		
		// 태그 추가
		public static function insertModelTag(	DataBaseConnect $con,
													$code, 
													$tag )
		{
			$query = 'INSERT INTO '.configs::TABLE_MODELTAGS."
						(
							`M_CODE`,
							`TAG`
						)VALUES(
							'$code',
							'$tag'
						);";
			return $con->query( $query );
		}
		
		// 태그 조회
		public static function selectModelTags( DataBaseConnect $con, $modelCode )
		{
			$query = 'SELECT * FROM '.configs::TABLE_MODELTAGS." WHERE `M_CODE`='$modelCode';";
			return $con->query( $query );
		}
		
		// 태그 삭제
		public static function deleteModelTag( DataBaseConnect $con, $seq )
		{
			$query = 'DELETE FROM '.configs::TABLE_MODELTAGS." WHERE `SEQ`='$seq' LIMIT 1;";
			return $con->query( $query );
		}
		
		// 태그 목록 조회
		public static function selectAllModelTags( DataBaseConnect $con )
		{
			$query = 'SELECT Distinct`TAG` FROM '.configs::TABLE_MODELTAGS.';';
			return $con->query( $query );
		}
	}
?>